/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package net.q1cc.cfs.jamp;

import java.io.File;
import java.net.URI;
import java.net.URISyntaxException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.swing.JFrame;
import javax.swing.JProgressBar;
import net.q1cc.cfs.jamp.schema.Song;
import xmlwise.Plist;
import xmlwise.XmlParseException;

/**
 *
 * @author claus
 */
public class DBImporter extends Thread{

    public static List<String> keys=Song.keys;

    String table_prefix;
    DBHandler dbHandler;
    String iTunesXMLLocation;


    public DBImporter(DBHandler dbHandler, String iTunesXMLLocation)
    {
        this.dbHandler=dbHandler;
        this.table_prefix=dbHandler.table_prefix;
        this.iTunesXMLLocation=iTunesXMLLocation;
    }


    public void  readiTunesToSQLDB() 
    {

        readiTunesTracks(iTunesXMLLocation);
        //TODO read Playlists etc
    }
    public void readiTunesTracks(String iTunesXMLLocation)
    {
        try {
            JProgressBar prog = new JProgressBar();
            prog.setIndeterminate(true);

            JFrame f = new JFrame("importing...");
            f.add(prog);
            f.pack();
            
            f.setLocationRelativeTo(Main.getInstance().bigGUI);
            f.setVisible(true);
            f.toFront();

            dbHandler.update("DELETE FROM '"+table_prefix+"tracks';");
            
            Map<String, Object> properties = Plist.load(iTunesXMLLocation);
            
            HashMap tracks = (HashMap) properties.get("Tracks");
            Collection values = tracks.values();
            prog.setMaximum(tracks.size());
            prog.setIndeterminate(false);
            prog.invalidate();
            int i2=0;
            
            boolean replace = false;
            String pathReplaced = "";
            String pathToReplaceWith = "";

            boolean firstOne=true;
            
            dbHandler.con.setAutoCommit(false);
            
            for(Object i:values)
            {
                if(firstOne)
                {
                    String loc= (String)((Map)i).get("Location");
                    firstOne=false;
                    URI uri;
                    try {
                        uri = new URI(loc);
                        String path = uri.toString();
                        //path = path.substring(1);
                        if(new File(path).exists())
                        {
                            //alles i.o.
                            
                        } else {
                            int liedPfadStart;
                            if(path.contains("iTunes%20Music"))
                                liedPfadStart=path.lastIndexOf("iTunes%20Music")+15;
                            else if (path.contains("iTunes%20Media"))
                                liedPfadStart=path.lastIndexOf("iTunes%20Media")+15;
                            else
                                break; //panik, die lieder sind in keinem standardordner.
                            
                            String liedPfad = path.substring(liedPfadStart);
                            File iTunesXML = new File(iTunesXMLLocation);
                            String iTunesFolder = iTunesXML.getParentFile().toURI().toString();
                            File testf= new File(new URI(iTunesFolder+"iTunes%20Music/"+liedPfad));
                            if(testf.exists())
                            {
                                pathReplaced = path.substring(0, liedPfadStart);
                                pathToReplaceWith=iTunesFolder+"iTunes%20Music/";
                                replace=true;
                            } else {
                                testf =new File(iTunesFolder+"iTunes%20Media/"+liedPfad);
                                if(testf.exists()){
                                    pathReplaced = path.substring(0, liedPfadStart-15);
                                    pathToReplaceWith=iTunesFolder+"/";
                                    
                                    replace=true;

                                } else {
                                    firstOne=true;
                                }
                            }

                        }
                    } catch (URISyntaxException ex) {
                        //dann halt nicht
                        ex.printStackTrace();
                    }

                }

                prog.setValue(i2);
                prog.invalidate();
                i2++;
                //System.out.println(i);
                HashMap obj = (HashMap)i;
                StringBuffer queryl = new StringBuffer("INSERT INTO '"+table_prefix+"tracks"+"' ('Track ID'");
                StringBuffer queryr = new StringBuffer("VALUES ('"+obj.get("Track ID")+"'");
                
                URI uri;
                for (String key:keys)
                {

                    if (obj.containsKey(key))
                    {
                        queryl.append(",'"+key+"'");
                        Object val = obj.get(key);
                        if (val instanceof Boolean)
                            val = ((Boolean) val).booleanValue() ? "1" : "0";
                        else
                        {
                            
                            if(key.equals("Location") && (replace))
                            {
                                try {
                                    uri = new URI(val.toString());
                                    String path = uri.toString();
                                    path=path.replace(pathReplaced, pathToReplaceWith);
                                    path=path.replace("\\", "/");
                                    uri=new URI(path);
                                    val=uri.toURL().toString();
                                    //TODO deal with iTunes being a dick and saving file names with upper/lowercase although they aren't
                                    val=((String)val).replace("file:/","file:///");
                                    //val=URLEncoder.encode(val.toString(), "UTF-8");
                                    
                                } catch (URISyntaxException ex) {
                                    ex.printStackTrace();
                                }
                                
                            }
                            val=val.toString().replace("'", "''");
                        }
                        queryr.append(",'"+val+"'");
                    }
                }


                String query = queryl+") "+queryr+");";
                System.out.println(query);
                int res=dbHandler.stmt.executeUpdate(query);
            }
            prog.setIndeterminate(true);
            dbHandler.con.commit();
            dbHandler.con.setAutoCommit(true);
            
            properties=null;
            tracks=null;
            keys=null;
            values=null;
            System.gc();
            dbHandler.useQuery(null);
            dbHandler.fireTableDataChanged();
            f.dispose();
            //System.out.println(properties);
        } catch (XmlParseException ex) {
           ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (java.io.IOException ex) {
            ex.printStackTrace();
        }
    }

    public void checkCreateTables()
    {
        try {
            ResultSet res = dbHandler.query("SELECT name from SQLITE_MASTER;");
            //System.out.println(res);
            ArrayList<String> tables = new ArrayList<String>();
            for(;res.next();)
            {
                tables.add(res.getString("name"));

            }
            if(!tables.contains(table_prefix+"tracks"))
            {
                // <editor-fold defaultstate="collapsed" desc="String cmd...">

                String cmd=
                        "CREATE TABLE '"+table_prefix+"tracks' (\n"+
  "'Track ID' int(11) NOT NULL,\n"+
  "'Persistent ID' varchar(50) NOT NULL,\n"+
  "'Album Rating Computed' tinyint(1) DEFAULT NULL,\n"+
  "'Kind' varchar(50) DEFAULT NULL,\n"+
  "'Year' int(11) DEFAULT NULL,\n"+
  "'File Folder Count' int(11) DEFAULT NULL,\n"+
  "'Genre' varchar(50) DEFAULT NULL,\n"+
  "'Date Modified' tinytext,\n"+
  "'Album Rating' int(11) DEFAULT NULL,\n"+
  "'Artist' tinytext ,\n"+
  "'Play Date' varchar(20) DEFAULT NULL,\n"+
  "'Album' varchar(100) DEFAULT NULL,\n"+
  "'Size' int(11) DEFAULT NULL,\n"+
  "'Play Date UTC' tinytext,\n"+
  "'Comments' text ,\n"+
  "'Sample Rate' int(11) DEFAULT NULL,\n"+
  "'Location' text ,\n"+
  "'Bit Rate' int(11) DEFAULT NULL,\n"+
  "'Date Added' tinytext,\n"+
  "'Name' tinytext ,\n"+
  "'Library Folder Count' int(11) DEFAULT NULL,\n"+
  "'Track Type' tinytext ,\n"+
  "'Total Time' int(11) DEFAULT NULL,\n"+
  "'Volume Adjustment' TINYINT UNSIGNED DEFAULT NULL,\n"+
  "'Rating' int(11) DEFAULT NULL,\n"+
  "'BPM' int(11) DEFAULT NULL,\n"+
  "'Play Count' int(11) DEFAULT NULL,\n"+
  "'Skip Count' int(11) DEFAULT NULL,\n"+
  "'Skip Date' tinytext DEFAULT NULL,\n"+
  "'Track Number' int(11) DEFAULT NULL,\n"+
  "'Composer' tinytext,\n"+
  "'Movie' boolean DEFAULT FALSE,\n"+
  "'TV Show' boolean DEFAULT FALSE,\n"+
  "'HD' boolean DEFAULT FALSE,\n"+
  "PRIMARY KEY ('Track ID')\n"+
  ");\n"
                        ; //</editor-fold>
                dbHandler.update(cmd);
            } //else ok
            //TODO implement playlists Table to the check
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    }
}
